

// 查询条件映射
const searchSql = {
  '0': (member = {}) => {
    const sql = `SELECT * FROM members LIMIT ? OFFSET ?`;
    const { pageNum, row, connection } = member;
    
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [rowT * 1, (pageNumT - 1) * rowT])

        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '1': (member = {}) => {
    const sql = `SELECT * FROM members WHERE projectName LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, projectName,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${projectName}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '2': (member = {}) => {
    const sql = `SELECT * FROM members WHERE phone LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, phone,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${phone}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  } ,
  '3': (member = {}) => {
    const sql = `SELECT * FROM members WHERE phone LIKE ? AND projectName LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, phone, projectName,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${phone}%`, `%${projectName}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '4': (member = {}) => {
    const sql = `SELECT * FROM members WHERE userName LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, userName,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${userName}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },
 '5': (member = {}) => {
    const sql = `SELECT * FROM members WHERE userName LIKE ? AND projectName LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, userName, projectName,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${userName}%`, `%${projectName}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },    
  '6': (member = {}) => {
    const sql = `SELECT * FROM members WHERE userName LIKE ? AND phone LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, userName, phone,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${userName}%`, `%${phone}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '7': (member = {}) => {
    const sql = `SELECT * FROM members WHERE userName LIKE ? AND phone LIKE ? AND projectName LIKE ? LIMIT ? OFFSET ?`;
    const { pageNum, row, userName, phone, projectName,connection } = member;
    const pageNumT = pageNum || 1;
    const rowT = row || 10;
    return new Promise( async (resolve, reject) => {
      try {
        const [ results, fields] = await connection.query(sql, [`%${userName}%`, `%${phone}%`, `%${projectName}%`, rowT * 1, (pageNumT - 1) * rowT])
        resolve([results, fields])
      }
      catch (error) {
        reject(error)
      }
    })
  }
}

// 查询总数映射
const totalSql = {
  '0': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members`
    const { connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql)
        const [ { total } ] = results
     
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '1': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE projectName LIKE ?`;
    const { projectName,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${projectName}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '2': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE phone LIKE ?`;
    const { phone,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${phone}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '3': (member = {}) => {
    const sql =  `SELECT COUNT(*) AS total FROM members WHERE phone LIKE ? AND projectName LIKE ?`;
    const { phone, projectName,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${phone}%`, `%${projectName}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '4': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE userName LIKE ?`;
    const { userName,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${userName}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '5': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE userName LIKE ? AND projectName LIKE ?`;
    const { userName, projectName,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${userName}%`, `%${projectName}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '6': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE userName LIKE ? AND phone LIKE ?`;
    const { userName, phone,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${userName}%`, `%${phone}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  },
  '7': (member = {}) => {
    const sql = `SELECT COUNT(*) AS total FROM members WHERE userName LIKE ? AND phone LIKE ? AND projectName LIKE ?`;
    const { userName, phone, projectName,connection } = member;
    return new Promise( async (resolve, reject) => {
      try {
        const [results, fields] = await connection.query(sql, [`%${userName}%`, `%${phone}%`, `%${projectName}%`])
        const [ { total } ] = results
        resolve(total)
      }
      catch (error) {
        reject(error)
      }
    })
  }
}

// 查询
exports.searchMember = async function(req, res, next) {
  const { userName, phone, projectName, pageNum, row } = req.query;
  
  try {
    const params = {
      userName,
      phone,
      projectName,
      pageNum,
      row,
      connection: req.connection
    }
    const [ [result, fields], total ] = await Promise.all([
      searchSql[assertZreo(req.query)](params),
      totalSql[assertZreo(req.query)](params)
    ])
    
    
    if(Array.isArray(result) && result.length > 0) {
      return res.resend.sendJson( req,res, {
        status: 200,
        code: 'SUCCESS',
        data: {
          data: createSendData(result, total, pageNum, row),
          token: req.newToken
        },
        msg: "查询成功",
      })
    }
    return res.resend.sendJson( req, res, {
      status: 200,
      code: 'SUCCESS',
      data: {
        data: createSendData([], 0, pageNum, row),
        token: req.newToken
      },
      msg: "查询成功",
    })
  }
  catch (error) {
    
    res.resend.sendJson(req, res, {
      status: 500,
      code: 'FAIL',
      data: '',
      msg: error.message,
      logMsg: error
    })
  }
}

// 条件判断
function assertZreo( member = {} ) {
  const { userName, phone, projectName } = member;
  if( !userName && !phone && !projectName) {
    return '0'
  }else if( !userName && !phone && projectName) {
  return '1'
  }else if( !userName && phone && !projectName) {
    return '2'  
  }else if( !userName && phone && projectName) {
    return '3'
  }
  else if( userName && !phone && !projectName) {
    return '4'
  }
  else if( userName && !phone && projectName) {
    return '5'
  }
  else if( userName && phone && !projectName) {
    return '6'
  }
  else if( userName && phone && projectName) {
    return '7'
  }
  return '0'
}


// 生产sendData对象
function createSendData( result, total, pageNum, row ) {
  const sendData = {
    total: total,
    list: result,
    pageNum: pageNum || 1,
    row: row || 10
  }
  return sendData
}


// user     phone    projectname

// 0        0           0 ;    `SELECT * FROM members LIMIT ? OFFSET ?`         

// 0        0           1     `SELECT * FROM members WHERE projectName = ? LIMIT ? OFFSET ?`
// 0        1           0     `SELECT * FROM members WHERE phone = ? LIMIT ? OFFSET ?`
// 1        0           0      `SELECT * FROM members WHERE userName = ? LIMIT ? OFFSET ?`

// 0        1           1      `SELECT * FROM members WHERE phone = ? AND projectName = ? LIMIT ? OFFSET ?`
// 1        0           1     `SELECT * FROM members WHERE userName = ? AND projectName = ? LIMIT ? OFFSET ?`
// 1        1           0     `SELECT * FROM members WHERE userName = ? AND phone = ? LIMIT ? OFFSET ?`

// 1        1           1      `SELECT * FROM members WHERE userName = ? AND phone = ? AND projectName = ? LIMIT ? OFFSET ?`